﻿/*
#**************************************************************************
#  openSIS is a student information system for public and non-public 
#  schools from Open Solutions for Education, Inc. web: www.os4ed.com
#
#  openSIS is  web-based, open source, and comes packed with features that 
#  include student demographic info, scheduling, grade book, attendance, 
#  report cards, eligibility, transcripts, parent portal, 
#  student portal and more.   
#
#  Visit the openSIS web site at http://www.opensis.com to learn more.
#  If you have question regarding this system or the license, please send 
#  an email to info@os4ed.com.
#
#  This program is released under the terms of the GNU General Public License  
#  as  published by the Free Software Foundation, version 2 of the License. 
#  See license.txt.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#  along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
#**************************************************************************** 

*/

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using OSISInstallationBusinessLayer;
using OpenSISDataConnection;
using Microsoft.SqlServer;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.IO;
using System.Data.OleDb;
using OSISGradeLevelBusinessLayer;
using System.Globalization;
using System.Resources;
using MySql.Data.MySqlClient;



namespace OSISInstallationDataLayer
{
    /// <summary>
    /// Summary description for InstallationDL
    /// </summary>
    public class InstallationDL
    {
        public InstallationDL()
        {
            //
            // TODO: Add constructor logic here
            //
        }


        public static string cnnstring;
        public static string serverinst = System.Configuration.ConfigurationManager.AppSettings["appserver"];


        string SQL_CONNECTION_STRING;
        string connectionString;
        string DBServer;

        private string DBName = "";
        private string DBCreateDataFile = "";

        protected bool bolDidPreviouslyConnect = false;

        private string fileUrl = "";
        private int timeout = 600;


        //private bool CreateInstanceDb(string strUname, string strPassword,string server)
        //{

        //    InstallationBL INBL = new InstallationBL();
        //    INBL.DatabaseType = server;

        //    if (INBL.DatabaseType == "msql")
        //    {
        //        string strSQL = "IF EXISTS (SELECT [name] FROM Master..sysdatabases WHERE [name] = N'" + DBName + "')" + Environment.NewLine + "BEGIN" + Environment.NewLine + "EXEC dbo.clearDBUsers " + DBName + "" + Environment.NewLine + "DROP DATABASE " + DBName + "" + Environment.NewLine + "END" + Environment.NewLine + "CREATE DATABASE " + DBName;


        //        bool bolIsConnecting = true;
        //        while (bolIsConnecting)
        //        {
        //            try
        //            {

        //                SqlConnection northwindConnection = new SqlConnection(SQL_CONNECTION_STRING);


        //                SqlCommand cmd = new SqlCommand(strSQL, northwindConnection);


        //                northwindConnection.Open();
        //                cmd.ExecuteNonQuery();
        //                northwindConnection.Close();

        //                bolIsConnecting = false;
        //                bolDidPreviouslyConnect = true;

        //                return true;
        //            }
        //            catch (SqlException sqlExc)
        //            {
        //                return false;

        //            }
        //            catch (Exception exc)
        //            {
        //                if (connectionString == SQL_CONNECTION_STRING)
        //                {
        //                }
        //                else
        //                {
        //                    return false;
        //                }
        //            }
        //        }
        //        return true;


        //    }
        //        //Mysql Implementaion
        //    else
        //    {
        //        string strSQL = "CREATE DATABASE IF NOT EXISTS `"+ DBName+"`";


        //        bool bolIsConnecting = true;
        //        while (bolIsConnecting)
        //        {
        //            try
        //            {

        //                MySqlConnection Connection = new MySqlConnection(SQL_CONNECTION_STRING);


        //                MySqlCommand cmd = new MySqlCommand(strSQL, Connection);


        //                Connection.Open();
        //                cmd.ExecuteNonQuery();
        //                Connection.Close();

        //                bolIsConnecting = false;
        //                bolDidPreviouslyConnect = true;

        //                return true;
        //            }
        //            catch (MySqlException sqlExc)
        //            {
        //                return false;

        //            }
        //            catch (Exception exc)
        //            {
        //                if (connectionString == SQL_CONNECTION_STRING)
        //                {
        //                }
        //                else
        //                {
        //                    return false;
        //                }
        //            }
        //        }
        //        return true;

        //    }


        //}

        //public bool CreateSQLDb(string strServerName, string strDBName, String strUserName, string strPass,string server)
        //{
        //    InstallationBL INBL = new InstallationBL();

        //    string servertype = server;

        //    if (servertype == "msql")
        //    {
        //        DBName = strDBName;
        //        DBServer = strServerName;
        //        if (strUserName.Trim().Length > 0)
        //        {
        //            SQL_CONNECTION_STRING = "User ID=" + strUserName + ";Pwd=" + strPass + ";Data Source=" + strServerName + ";database=;Persist Security Info=False;";
        //        }
        //        else
        //        {
        //            SQL_CONNECTION_STRING = "Server=" + DBServer + ";" + "DataBase=;" + "Integrated Security=SSPI;";
        //        }
        //        connectionString = SQL_CONNECTION_STRING;
        //        if (CreateInstanceDb(strUserName, strPass,server))
        //        {
        //            return true;
        //        }
        //        else
        //        {
        //            return false;
        //        }

        //    }
        //        //MYSQL Implementation
        //    else
        //    {
        //        DBName = strDBName;
        //        DBServer = strServerName;
        //        if (strUserName.Trim().Length > 0)
        //        {

        //            SQL_CONNECTION_STRING = "uid=" + strUserName + ";pwd=" + strPass + ";Data Source=" + strServerName + " ;Character Set=utf8;";
        //        }
        //        else
        //        {

        //        }
        //        connectionString = SQL_CONNECTION_STRING;
        //        if (CreateInstanceDb(strUserName, strPass,server))
        //        {
        //            return true;
        //        }
        //        else
        //        {
        //            return false;
        //        }



        //    }







        //}

        //public string ConnString
        //{

        //    get { return connectionString; }
        //    set
        //    {
        //        connectionString = value;
        //        SQL_CONNECTION_STRING = value;
        //    }
        //}


        //public bool ExecuteSQLScript(string SQlFilePath, string ConStr,string server)
        //{

        //    InstallationBL INBL = new InstallationBL();
        //    INBL.DatabaseType = server;
        //    try
        //    {

        //        fileUrl = SQlFilePath;
        //        System.Net.WebRequest request = System.Net.WebRequest.Create(fileUrl);
        //        using (StreamReader sr = new StreamReader(request.GetResponse().GetResponseStream()))
        //        {
        //            if (INBL.DatabaseType == "msql")
        //            {
        //                SqlConnection conn = null;
        //                conn = new SqlConnection(ConStr);

        //                conn.Open();

        //                while (!sr.EndOfStream)
        //                {
        //                    System.Text.StringBuilder sb = new System.Text.StringBuilder();
        //                    SqlCommand cmd = conn.CreateCommand();

        //                    while (!sr.EndOfStream)
        //                    {
        //                        string s = sr.ReadLine();
        //                        if (s != null && s.ToUpper().Trim().Equals("GO"))
        //                        {
        //                            break;
        //                        }

        //                        sb.AppendLine(s);
        //                    }


        //                    cmd.CommandText = sb.ToString();
        //                    cmd.CommandTimeout = timeout;


        //                    cmd.ExecuteNonQuery();


        //                }



        //                string path = HttpContext.Current.Server.MapPath("~/Web.config");


        //                System.Xml.XmlDocument xDoc = new System.Xml.XmlDocument();

        //                xDoc.Load(path);

        //                System.Xml.XmlNodeList nodeList = xDoc.GetElementsByTagName("appSettings");

        //                System.Xml.XmlNodeList nodeAppSettings = nodeList[0].ChildNodes;

        //                System.Xml.XmlAttributeCollection xmlAttCollection = nodeAppSettings[0].Attributes;

        //                xmlAttCollection[0].InnerXml = "sqlConn"; // for key attribute

        //                xmlAttCollection[1].InnerXml = "Persist Security Info=False;Provider=SQLOLEDB;" + ConStr; // for value attribute



        //                System.Xml.XmlNodeList nodeAppSettingsserv = nodeList[0].ChildNodes;

        //                System.Xml.XmlAttributeCollection xmlAttCollectionserv = nodeAppSettingsserv[1].Attributes;


        //                xmlAttCollectionserv[0].InnerXml = "appserver"; // for key attribute

        //                xmlAttCollectionserv[1].InnerXml = "MSSQLServer";


        //                System.Xml.XmlNodeList nodeAppSettingscal = nodeList[0].ChildNodes;

        //                System.Xml.XmlAttributeCollection xmlAttCollectioncal = nodeAppSettingscal[2].Attributes;


        //                xmlAttCollectioncal[0].InnerXml = "calendarServer"; // for key attribute

        //                xmlAttCollectioncal[1].InnerXml = "MSSQLServer"; 


        //                xDoc.Save(path);

        //                ////static connection string
        //                cnnstring = "Persist Security Info=False;Provider=SQLOLEDB;" + ConStr;

        //                serverinst = "MSSQLServer"; 

        //            }

        //                //MYSQL Implementation
        //            else
        //            {
        //                MySqlConnection conn = null;
        //                conn = new MySqlConnection(ConStr);

        //                conn.Open();

        //                while (!sr.EndOfStream)
        //                {
        //                    System.Text.StringBuilder sb = new System.Text.StringBuilder();
        //                    MySqlCommand cmd = conn.CreateCommand();

        //                    while (!sr.EndOfStream)
        //                    {
        //                        string s = sr.ReadLine();
        //                        if (s != null && s.ToUpper().Trim().Equals("GO"))
        //                        {
        //                            break;
        //                        }

        //                        sb.AppendLine(s);
        //                        if (s.Contains("INNODB"))
        //                        {
        //                            cmd.CommandText = sb.ToString();
        //                            cmd.CommandTimeout = timeout;


        //                            cmd.ExecuteNonQuery();

        //                            sb.Remove(0, sb.Length);

        //                        }
        //                    }







        //                }



        //                string path = HttpContext.Current.Server.MapPath("~/Web.config");


        //                System.Xml.XmlDocument xDoc = new System.Xml.XmlDocument();

        //                xDoc.Load(path);

        //                System.Xml.XmlNodeList nodeList = xDoc.GetElementsByTagName("appSettings");

        //                System.Xml.XmlNodeList nodeAppSettings = nodeList[0].ChildNodes;


        //                System.Xml.XmlAttributeCollection xmlAttCollection = nodeAppSettings[0].Attributes;



        //                xmlAttCollection[0].InnerXml = "sqlConn"; // for key attribute

        //                xmlAttCollection[1].InnerXml =  ConStr; // for value attribute


        //                System.Xml.XmlNodeList nodeAppSettingsserv = nodeList[0].ChildNodes;

        //                System.Xml.XmlAttributeCollection xmlAttCollectionserv = nodeAppSettingsserv[1].Attributes;


        //                xmlAttCollectionserv[0].InnerXml = "appserver"; // for key attribute

        //                xmlAttCollectionserv[1].InnerXml = "MySQL";



        //                System.Xml.XmlNodeList nodeAppSettingscal = nodeList[0].ChildNodes;

        //                System.Xml.XmlAttributeCollection xmlAttCollectioncal = nodeAppSettingscal[2].Attributes;


        //                xmlAttCollectioncal[0].InnerXml = "calendarServer"; // for key attribute

        //                xmlAttCollectioncal[1].InnerXml = "MySQL"; 


        //                xDoc.Save(path);

        //                ////static connection string
        //                cnnstring =  ConStr;

        //                serverinst = "MySQL"; 

        //            }



        //        }

        //        return true;
        //    }
        //    catch (Exception ex)
        //    {

        //        return false;
        //    }
        //    finally
        //    {
        //    }
        //}







        public void InsertInstalsettings(InstallationBL INBL)
        {
            string sql;
            if (serverinst == "MSSQLServer")
            {
                sql = "Insert into installation values(1, N'" + INBL.Language + "', N'" + INBL.Dateformat + "',N'" + INBL.DatabaseType + "',N'" + INBL.Server + "',N'" + INBL.Databasename + "',N'" + INBL.DatabaseUsername + "',N'" + INBL.DatabasePassword + "',N'" + INBL.Port + "',N'" + INBL.AdminUsername + "',N'" + INBL.AdminPassword + "',N'" + INBL.TimeZone + "')";
            }
            else
            {
                sql = "Insert into installation values(1, '" + INBL.Language + "', '" + INBL.Dateformat + "','" + INBL.DatabaseType + "','" + INBL.Server + "','" + INBL.Databasename + "','" + INBL.DatabaseUsername + "','" + INBL.DatabasePassword + "','" + INBL.Port + "','" + INBL.AdminUsername + "','" + INBL.AdminPassword + "','" + INBL.TimeZone + "')";
            }
            // DataConnection.ConnectionPath.ExecuteCommandafterinstall(sql, cnnstring, serverinst);
            DataConnection.ConnectionPath.ExecuteCommand(sql);

        }

        public DataSet ValidateAdmin(InstallationBL INBL)
        {


            string sql = "Select COUNT (*) from installation where admin_username = N'" + INBL.AdminUsername + "' and admin_password = N'" + INBL.AdminPassword + "'";

            DataSet ds = DataConnection.ConnectionPath.Getdata(sql);

            return ds;

        }



        public int AddSchoolName(InstallationBL INBL)
        {
            DataSet ds = null;
            object id;

            if (serverinst == "MSSQLServer")
            {

                //  ds = DataConnection.ConnectionPath.Getdataafterinstall("exec getMax sch_id,schools", cnnstring, serverinst);
                ds = DataConnection.ConnectionPath.Getdata("exec getMax sch_id,schools");

                String strSchoolAdd = "INSERT into schools(sch_id,sch_short_nm,sch_full_nm,sch_addr1,sch_addr2,sch_city,sch_st,sch_zip,sch_fax,sch_phn,sch_url,sch_ceeb_nbr,sch_type,sch_ed_lvl,sch_po_box_zip,sch_po_box_st,sch_po_box_city,sch_po_box,sch_emer_phn,last_updated,sch_email,sch_principal,sch_base_grading_scale,sch_country,sch_po_box_addr1,sch_po_box_addr2,sch_po_box_country) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",N'" + "" + "',N'" + INBL.SchoolName.Replace("'", "''") + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "')";

                //DataConnection.ConnectionPath.ExecuteCommandafterinstall(strSchoolAdd, cnnstring, serverinst);
                DataConnection.ConnectionPath.ExecuteCommand(strSchoolAdd);

                // id = DataConnection.ConnectionPath.Getdataafterinstall("SELECT max(sch_id) from schools", cnnstring, serverinst).Tables[0].Rows[0][0];

                id = DataConnection.ConnectionPath.Getdata("SELECT max(sch_id) from schools").Tables[0].Rows[0][0];
                return Convert.ToInt32(id);


            }
            else
            {

                // ds = DataConnection.ConnectionPath.Getdataafterinstall("select IFNULL(max(sch_id),0) + 1 as sch_id from schools", cnnstring, serverinst);
                ds = DataConnection.ConnectionPath.Getdata("select IFNULL(max(sch_id),0) + 1 as sch_id from schools");

                String strSchoolAdd = "INSERT into schools(sch_id,sch_short_nm,sch_full_nm,sch_addr1,sch_addr2,sch_city,sch_zip,sch_fax,sch_phn,sch_url,sch_type,sch_ed_lvl,sch_po_box_zip,sch_po_box_city,sch_po_box,sch_emer_phn,sch_email,sch_principal,sch_base_grading_scale,sch_po_box_addr1,sch_po_box_addr2,sch_ceeb_nbr,sch_min_grade_id,sch_max_grade_id) values( '" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "',N'" + "" + "',N'" + INBL.SchoolName.Replace("'", "''") + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',N'" + "" + "',0,null,null)";

                //DataConnection.ConnectionPath.ExecuteCommandafterinstall(strSchoolAdd, cnnstring, serverinst);

                //id = DataConnection.ConnectionPath.Getdataafterinstall("SELECT max(sch_id) from schools", cnnstring, serverinst).Tables[0].Rows[0][0];


                DataConnection.ConnectionPath.ExecuteCommand(strSchoolAdd);

                id = DataConnection.ConnectionPath.Getdata("SELECT max(sch_id) from schools").Tables[0].Rows[0][0];


                return Convert.ToInt32(id);

            }

        }


        public int InsertBeginEnddate(InstallationBL INBL)
        {
            DataSet ds = null;
            object id;

            int yrtypeid = 1;

            if (serverinst == "MSSQLServer")
            {
                //ds = DataConnection.ConnectionPath.Getdataafterinstall("exec getMax schyr_id,school_years", cnnstring, serverinst);
                ds = DataConnection.ConnectionPath.Getdata("exec getMax schyr_id,school_years");

                String strSchoolYear = "INSERT into school_years(schyr_id,schyr_start_date,schyr_end_date,schyr_short_nm,schyr_nm,schyr_year,last_updated,schyr_next_year,schyr_prev_year,years_type_id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",N'" + INBL.BeginDate.Replace("'", "''") + "',N'" + INBL.EndDate.Replace("'", "''") + "',N'" + "" + "',N'" + INBL.SchoolYearName + "',N'" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "',N'" + DateTime.Now.ToString("MM/dd/yyyy") + "',N'" + "" + "',N'" + "" + "'," + yrtypeid + ")";

                //DataConnection.ConnectionPath.ExecuteCommandafterinstall(strSchoolYear, cnnstring, serverinst);
                //id = DataConnection.ConnectionPath.Getdataafterinstall("SELECT max(schyr_id) from school_years", cnnstring, serverinst).Tables[0].Rows[0][0];


                DataConnection.ConnectionPath.ExecuteCommand(strSchoolYear);
                id = DataConnection.ConnectionPath.Getdata("SELECT max(schyr_id) from school_years").Tables[0].Rows[0][0];


                return Convert.ToInt32(id);

            }
            else
            {

                DateTime s = Convert.ToDateTime(INBL.BeginDate, DateTimeFormatInfo.InvariantInfo);
                INBL.BeginDate = s.ToString("yyyy/MM/dd");

                DateTime d = Convert.ToDateTime(INBL.EndDate, DateTimeFormatInfo.InvariantInfo);
                INBL.EndDate = d.ToString("yyyy/MM/dd");

                //ds = DataConnection.ConnectionPath.Getdataafterinstall("select IFNULL(max(schyr_id),0) + 1 as schyr_id from school_years", cnnstring, serverinst);



                ds = DataConnection.ConnectionPath.Getdata("select IFNULL(max(schyr_id),0) + 1 as schyr_id from school_years");


                String strSchoolYear = "INSERT into school_years(schyr_id,schyr_start_date,schyr_end_date,schyr_short_nm,schyr_nm,schyr_year,last_updated,schyr_next_year,schyr_prev_year,years_type_id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",N'" + INBL.BeginDate.Replace("'", "''") + "',N'" + INBL.EndDate.Replace("'", "''") + "',N'" + "" + "',N'" + INBL.SchoolYearName + "',N'" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "',N'" + DateTime.Now.ToString("yyyy/MM/dd") + "',0,0," + yrtypeid + ")";

                //DataConnection.ConnectionPath.ExecuteCommandafterinstall(strSchoolYear, cnnstring, serverinst);
                //id = DataConnection.ConnectionPath.Getdataafterinstall("SELECT max(schyr_id) from school_years", cnnstring, serverinst).Tables[0].Rows[0][0];


                DataConnection.ConnectionPath.ExecuteCommand(strSchoolYear);
                id = DataConnection.ConnectionPath.Getdata("SELECT max(schyr_id) from school_years").Tables[0].Rows[0][0];


                return Convert.ToInt32(id);

            }



        }



        public void InsertintoMarkingPeriod(InstallationBL INBL)
        {

            DataSet ds = null;

            if (serverinst == "MSSQLServer")
            {
                //ds = DataConnection.ConnectionPath.Getdataafterinstall("exec getMax sch_marking_period_id,sch_marking_periods", cnnstring, serverinst);
                ds = DataConnection.ConnectionPath.Getdata("exec getMax sch_marking_period_id,sch_marking_periods");

                string s = INBL.Language.ToString();
                string year = "";
                string initials = "";
                switch (s)
                {
                    case "en":
                        year = "Full Year";
                        initials = "FY";
                        break;
                    case "es":
                        year = "Año completo";
                        initials = "Ac";
                        break;
                    case "zh-CHS":
                        year = "全年";
                        initials = "FY";
                        break;
                    case "ja":
                        year = "通期";
                        initials = "FY";
                        break;
                    case "de":
                        year = "Full Year";
                        initials = "FY";
                        break;
                    case "ko":
                        year = "전체 년도";
                        initials = "FY";
                        break;
                    case "fr":
                        year = "Année complète";
                        initials = "Ac";
                        break;
                    case "it":
                        year = "Full Anno";
                        initials = "FA";
                        break;
                    case "ru":
                        year = "Полный Год";
                        initials = "ПГ";
                        break;
                    case "pt":
                        year = "Ano inteiro";
                        initials = "Ai";
                        break;
                    case "ar":
                        year = "سنة كاملة";
                        initials = "FY";
                        break;


                }



                String strSchoolYear = "INSERT into sch_marking_periods(sch_marking_period_id,sch_marking_period_type,sch_mp_start_date,sch_mp_end_date,sch_mp_attendance_ind,sch_mp_grading_ind,sch_mp_grd_start_date,sch_mp_grd_end_date,sch_id,schyr_id,sch_mp_name,sch_mp_short_nm,last_updated) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",'Y','" + INBL.BeginDate.Replace("'", "''") + "','" + INBL.EndDate.Replace("'", "''") + "','Y','Y','" + INBL.BeginDate.Replace("'", "''") + "','" + INBL.EndDate.Replace("'", "''") + "'," + INBL.Schoolid + "," + INBL.Schoolyrid + ",N'" + year + "',N'" + initials + "','" + DateTime.Now.ToString("MM/dd/yyyy") + "')";

                //DataConnection.ConnectionPath.ExecuteCommandafterinstall(strSchoolYear, cnnstring, serverinst);
                DataConnection.ConnectionPath.ExecuteCommand(strSchoolYear);
            }
            else
            {

                //ds = DataConnection.ConnectionPath.Getdataafterinstall("select IFNULL(max(sch_marking_period_id),0) + 1 as sch_marking_period_id from sch_marking_periods", cnnstring, serverinst);


                ds = DataConnection.ConnectionPath.Getdata("select IFNULL(max(sch_marking_period_id),0) + 1 as sch_marking_period_id from sch_marking_periods");



                string s = INBL.Language.ToString();
                string year = "";
                string initials = "";
                switch (s)
                {
                    case "en":
                        year = "Full Year";
                        initials = "FY";
                        break;
                    case "es":
                        year = "Año completo";
                        initials = "Ac";
                        break;
                    case "zh-CHS":
                        year = "全年";
                        initials = "FY";
                        break;
                    case "ja":
                        year = "通期";
                        initials = "FY";
                        break;
                    case "de":
                        year = "Full Year";
                        initials = "FY";
                        break;
                    case "ko":
                        year = "전체 년도";
                        initials = "FY";
                        break;
                    case "fr":
                        year = "Année complète";
                        initials = "Ac";
                        break;
                    case "it":
                        year = "Full Anno";
                        initials = "FA";
                        break;
                    case "ru":
                        year = "Полный Год";
                        initials = "ПГ";
                        break;
                    case "pt":
                        year = "Ano inteiro";
                        initials = "Ai";
                        break;
                    case "ar":
                        year = "سنة كاملة";
                        initials = "FY";
                        break;


                }



                String strSchoolYear = "INSERT into sch_marking_periods(sch_marking_period_id,sch_marking_period_type,sch_mp_start_date,sch_mp_end_date,sch_mp_attendance_ind,sch_mp_grading_ind,sch_mp_grd_start_date,sch_mp_grd_end_date,sch_id,schyr_id,sch_mp_name,sch_mp_short_nm,last_updated) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",'Y','" + INBL.BeginDate.Replace("'", "''") + "','" + INBL.EndDate.Replace("'", "''") + "','Y','Y','" + INBL.BeginDate.Replace("'", "''") + "','" + INBL.EndDate.Replace("'", "''") + "'," + INBL.Schoolid + "," + INBL.Schoolyrid + ",N'" + year + "',N'" + initials + "','" + DateTime.Now.ToString("yyyy/MM/dd") + "')";

                //DataConnection.ConnectionPath.ExecuteCommandafterinstall(strSchoolYear, cnnstring, serverinst);
                DataConnection.ConnectionPath.ExecuteCommand(strSchoolYear);
            }



        }
        //****************(10/07/09)****************************

        public int AddGradeLevels(InstallationBL INBL)
        {
            DataSet ds = null;
            object id;
            DataSet dsShortOrder = null;
            if (serverinst == "MSSQLServer")
            {
                //ds = DataConnection.ConnectionPath.Getdataafterinstall("exec getMax grd_lvl_id,grade_levels", cnnstring, serverinst);

                //dsShortOrder = DataConnection.ConnectionPath.Getdataafterinstall("select isnull(max(grd_lvl_cd),0) + 1 as short_order from grade_levels where sch_id='" + INBL.Schoolid + "'", cnnstring, serverinst);




                ds = DataConnection.ConnectionPath.Getdata("exec getMax grd_lvl_id,grade_levels");

                dsShortOrder = DataConnection.ConnectionPath.Getdata("select isnull(max(grd_lvl_cd),0) + 1 as short_order from grade_levels where sch_id='" + INBL.Schoolid + "'");




                String strAddGradeLevel = "INSERT into grade_levels(grd_lvl_id,grd_lvl_cd,grd_lvl_short_nm,grd_lvl_nm,grd_lvl_id_next,last_updated,grd_lvl_id_prev,sch_id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",N'" + Convert.ToInt32(dsShortOrder.Tables[0].Rows[0][0]) + "',N'" + INBL.Gradename + "',N'','','',''," + INBL.Schoolid + ")";

                //DataConnection.ConnectionPath.ExecuteCommandafterinstall(strAddGradeLevel, cnnstring, serverinst);
                //id = DataConnection.ConnectionPath.Getdataafterinstall("SELECT max(grd_lvl_id) from grade_levels where sch_id = " + INBL.Schoolid, cnnstring, serverinst).Tables[0].Rows[0][0];

                DataConnection.ConnectionPath.ExecuteCommand(strAddGradeLevel);
                id = DataConnection.ConnectionPath.Getdata("SELECT max(grd_lvl_id) from grade_levels where sch_id = " + INBL.Schoolid).Tables[0].Rows[0][0];


                return Convert.ToInt32(id);
            }
            else
            {

                //ds = DataConnection.ConnectionPath.Getdataafterinstall("select IFNULL(max(grd_lvl_id),0) + 1 as grd_lvl_id from grade_levels", cnnstring, serverinst);


                //dsShortOrder = DataConnection.ConnectionPath.Getdataafterinstall("select IFNULL(max(grd_lvl_cd),0) + 1 as short_order from grade_levels where sch_id='" + INBL.Schoolid + "'", cnnstring, serverinst);



                ds = DataConnection.ConnectionPath.Getdata("select IFNULL(max(grd_lvl_id),0) + 1 as grd_lvl_id from grade_levels");


                dsShortOrder = DataConnection.ConnectionPath.Getdata("select IFNULL(max(grd_lvl_cd),0) + 1 as short_order from grade_levels where sch_id='" + INBL.Schoolid + "'");




                String strAddGradeLevel = "INSERT into grade_levels(grd_lvl_id,grd_lvl_cd,grd_lvl_short_nm,grd_lvl_nm,grd_lvl_id_next,last_updated,grd_lvl_id_prev,sch_id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",N'" + Convert.ToInt32(dsShortOrder.Tables[0].Rows[0][0]) + "',N'" + INBL.Gradename + "',N'',null,null,null," + INBL.Schoolid + ")";

                //DataConnection.ConnectionPath.ExecuteCommandafterinstall(strAddGradeLevel, cnnstring, serverinst);
                //id = DataConnection.ConnectionPath.Getdataafterinstall("SELECT max(grd_lvl_id) from grade_levels where sch_id = " + INBL.Schoolid, cnnstring, serverinst).Tables[0].Rows[0][0];



                DataConnection.ConnectionPath.ExecuteCommand(strAddGradeLevel);
                id = DataConnection.ConnectionPath.Getdata("SELECT max(grd_lvl_id) from grade_levels where sch_id = " + INBL.Schoolid).Tables[0].Rows[0][0];


                return Convert.ToInt32(id);

            }


        }



        public void UpdateMaxGrade(InstallationBL INBL)
        {

            string strUpdateMaxGrade = "update schools set sch_max_grade_id= N'" + INBL.Maxgrade + "' where sch_id=" + INBL.Schoolid + "";

            //DataConnection.ConnectionPath.ExecuteCommandafterinstall(strUpdateMaxGrade, cnnstring, serverinst);
            DataConnection.ConnectionPath.ExecuteCommand(strUpdateMaxGrade);

        }

        public void UpdateMinGrade(InstallationBL INBL)
        {

            string strUpdateMinGrade = "update schools set sch_min_grade_id= N'" + INBL.Mingrade + "' where sch_id=" + INBL.Schoolid + "";

            //DataConnection.ConnectionPath.ExecuteCommandafterinstall(strUpdateMinGrade, cnnstring, serverinst);
            DataConnection.ConnectionPath.ExecuteCommand(strUpdateMinGrade);

        }

        public DataTable GetDateFormat(InstallationBL INBL)
        {


            string sql = "select date_format_code from installation";

            DataTable dt = DataConnection.ConnectionPath.Getdata(sql).Tables[0];

            return dt;

        }

        //********************18/07/09*******************

        public DataTable GetTimeJoneCode()
        {


            string sql = "select time_zone_code from installation";

            DataTable dt = DataConnection.ConnectionPath.Getdata(sql).Tables[0];

            return dt;

        }



        public int InsertBeginEnddateschooladd(InstallationBL INBL)
        {
            string server = System.Configuration.ConfigurationManager.AppSettings["appserver"];

            if (server == "MSSQLServer")
            {
                DataSet ds = null;
                object id;

                int yrtypeid = 1;

                ds = DataConnection.ConnectionPath.Getdata("exec getMax schyr_id,school_years");

                String strSchoolYear = "INSERT into school_years(schyr_id,schyr_start_date,schyr_end_date,schyr_short_nm,schyr_nm,schyr_year,last_updated,schyr_next_year,schyr_prev_year,years_type_id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",N'" + INBL.BeginDate.Replace("'", "''") + "',N'" + INBL.EndDate.Replace("'", "''") + "',N'" + "" + "',N'" + INBL.SchoolYearName + "',N'" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "',N'" + DateTime.Now.ToString("MM/dd/yyyy", DateTimeFormatInfo.InvariantInfo) + "',N'" + "" + "',N'" + "" + "'," + yrtypeid + ")";

                DataConnection.ConnectionPath.ExecuteCommand(strSchoolYear);
                id = DataConnection.ConnectionPath.Getdata("SELECT max(schyr_id) from school_years").Tables[0].Rows[0][0];
                return Convert.ToInt32(id);
            }

            if (server == "MySQL")
            {
                DataSet ds = null;
                object id;

                int yrtypeid = 1;

                ds = DataConnection.ConnectionPath.Getdata("CALL getmax('schyr_id','school_years')");



                DateTime s = Convert.ToDateTime(INBL.BeginDate, DateTimeFormatInfo.InvariantInfo);
                INBL.BeginDate = s.ToString("yyyy/MM/dd");

                DateTime d = Convert.ToDateTime(INBL.EndDate, DateTimeFormatInfo.InvariantInfo);
                INBL.EndDate = d.ToString("yyyy/MM/dd");



                String strSchoolYear = "INSERT into school_years(schyr_id,schyr_start_date,schyr_end_date,schyr_short_nm,schyr_nm,schyr_year,last_updated,schyr_next_year,schyr_prev_year,years_type_id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",N'" + INBL.BeginDate.Replace("'", "''") + "',N'" + INBL.EndDate.Replace("'", "''") + "',N'" + "" + "',N'" + INBL.SchoolYearName + "',N'" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "',N'" + DateTime.Now.ToString("yyyy/MM/dd", DateTimeFormatInfo.InvariantInfo) + "',0,0," + yrtypeid + ")";

                DataConnection.ConnectionPath.ExecuteCommand(strSchoolYear);
                id = DataConnection.ConnectionPath.Getdata("SELECT max(schyr_id) from school_years").Tables[0].Rows[0][0];
                return Convert.ToInt32(id);
            }
            else
            {
                DataSet ds = null;
                object id;

                int yrtypeid = 1;

                ds = DataConnection.ConnectionPath.Getdata("exec getMax schyr_id,school_years");

                String strSchoolYear = "INSERT into school_years(schyr_id,schyr_start_date,schyr_end_date,schyr_short_nm,schyr_nm,schyr_year,last_updated,schyr_next_year,schyr_prev_year,years_type_id) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",N'" + INBL.BeginDate.Replace("'", "''") + "',N'" + INBL.EndDate.Replace("'", "''") + "',N'" + "" + "',N'" + INBL.SchoolYearName + "',N'" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "',N'" + DateTime.Now.ToString("MM/dd/yyyy", DateTimeFormatInfo.InvariantInfo) + "',N'" + "" + "',N'" + "" + "'," + yrtypeid + ")";

                DataConnection.ConnectionPath.ExecuteCommand(strSchoolYear);
                id = DataConnection.ConnectionPath.Getdata("SELECT max(schyr_id) from school_years").Tables[0].Rows[0][0];
                return Convert.ToInt32(id);
            }



        }



        public void InsertintoMarkingPeriodschooladd(InstallationBL INBL)
        {
             string server = System.Configuration.ConfigurationManager.AppSettings["appserver"];
           // string server = "MSSQLServer";


            if (server == "MSSQLServer")
            {
                DataSet ds = null;
                ds = DataConnection.ConnectionPath.Getdata("exec getMax sch_marking_period_id,sch_marking_periods");
                //  string s = INBL.Language.ToString();
                string s = "en-US";
                string year = "";
                string initials = "";
                switch (s)
                {
                    case "en-US":
                        year = "Full Year";
                        initials = "FY";
                        break;
                    case "es-MX":
                        year = "Año completo";
                        initials = "Ac";
                        break;
                    case "zh-CN":
                        year = "全年";
                        initials = "FY";
                        break;
                    case "ja-JP":
                        year = "通期";
                        initials = "FY";
                        break;
                    case "de-DE":
                        year = "Full Year";
                        initials = "FY";
                        break;
                    case "ko-KR":
                        year = "전체 년도";
                        initials = "FY";
                        break;
                    case "fr-FR":
                        year = "Année complète";
                        initials = "Ac";
                        break;
                    case "it-IT":
                        year = "Full Anno";
                        initials = "FA";
                        break;
                    case "ru-RU":
                        year = "Полный Год";
                        initials = "ПГ";
                        break;
                    case "pt-BR":
                        year = "Ano inteiro";
                        initials = "Ai";
                        break;
                    case "ar-SA":
                        year = "سنة كاملة";
                        initials = "FY";
                        break;
                }

                String strSchoolYear = "INSERT into sch_marking_periods(sch_marking_period_id,sch_marking_period_type,sch_mp_start_date,sch_mp_end_date,sch_mp_attendance_ind,sch_mp_grading_ind,sch_mp_grd_start_date,sch_mp_grd_end_date,sch_id,schyr_id,sch_mp_name,sch_mp_short_nm,last_updated) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",'Y','" + INBL.BeginDate.Replace("'", "''") + "','" + INBL.EndDate.Replace("'", "''") + "','Y','Y','" + INBL.BeginDate.Replace("'", "''") + "','" + INBL.EndDate.Replace("'", "''") + "'," + INBL.Schoolid + "," + INBL.Schoolyrid + ",N'" + year + "',N'" + initials + "','" + DateTime.Now.ToString("MM/dd/yyyy", DateTimeFormatInfo.InvariantInfo) + "')";

                DataConnection.ConnectionPath.ExecuteCommand(strSchoolYear);

            }
            if (server == "MySQL")
            {
                DataSet ds = null;

                ds = DataConnection.ConnectionPath.Getdata("CALL getmax('sch_marking_period_id','sch_marking_periods')");



                DateTime b = Convert.ToDateTime(INBL.BeginDate, DateTimeFormatInfo.InvariantInfo);
                INBL.BeginDate = b.ToString("yyyy/MM/dd");

                DateTime d = Convert.ToDateTime(INBL.EndDate, DateTimeFormatInfo.InvariantInfo);
                INBL.EndDate = d.ToString("yyyy/MM/dd");




                string s = INBL.Language.ToString();
                string year = "";
                string initials = "";
                switch (s)
                {
                    case "en-US":
                        year = "Full Year";
                        initials = "FY";
                        break;
                    case "es-MX":
                        year = "Año completo";
                        initials = "Ac";
                        break;
                    case "zh-CN":
                        year = "全年";
                        initials = "FY";
                        break;
                    case "ja-JP":
                        year = "通期";
                        initials = "FY";
                        break;
                    case "de-DE":
                        year = "Full Year";
                        initials = "FY";
                        break;
                    case "ko-KR":
                        year = "전체 년도";
                        initials = "FY";
                        break;
                    case "fr-FR":
                        year = "Année complète";
                        initials = "Ac";
                        break;
                    case "it-IT":
                        year = "Full Anno";
                        initials = "FA";
                        break;
                    case "ru-RU":
                        year = "Полный Год";
                        initials = "ПГ";
                        break;
                    case "pt-BR":
                        year = "Ano inteiro";
                        initials = "Ai";
                        break;
                    case "ar-SA":
                        year = "سنة كاملة";
                        initials = "FY";
                        break;
                }

                String strSchoolYear = "INSERT into sch_marking_periods(sch_marking_period_id,sch_marking_period_type,sch_mp_start_date,sch_mp_end_date,sch_mp_attendance_ind,sch_mp_grading_ind,sch_mp_grd_start_date,sch_mp_grd_end_date,sch_id,schyr_id,sch_mp_name,sch_mp_short_nm,last_updated) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",'Y','" + INBL.BeginDate.Replace("'", "''") + "','" + INBL.EndDate.Replace("'", "''") + "','Y','Y','" + INBL.BeginDate.Replace("'", "''") + "','" + INBL.EndDate.Replace("'", "''") + "'," + INBL.Schoolid + "," + INBL.Schoolyrid + ",N'" + year + "',N'" + initials + "','" + DateTime.Now.ToString("yyyy/MM/dd", DateTimeFormatInfo.InvariantInfo) + "')";

                DataConnection.ConnectionPath.ExecuteCommand(strSchoolYear);
            }
            else
            {

            }






        }


        //mysql implementation
        public DataTable CheckMysqlDB(InstallationBL INBL)
        {
            DataTable dt = null;




            dt = DataConnection.ConnectionPath.CheckDatabase("SHOW DATABASES like '" + INBL.Databasename + "'", INBL.Conn).Tables[0];

            return dt;

        }

        //added on 22nd september

        public void InsertintoSchoolspeccat(InstallationBL INBL)
        {
            if (serverinst == "MSSQLServer")
            {
                DataSet ds = new DataSet();
                //ds = DataConnection.ConnectionPath.Getdataafterinstall("exec getMax Sch_Spe_Id,Student_Setup_School_Specific_Category", cnnstring, serverinst);

                ds = DataConnection.ConnectionPath.Getdata("exec getMax Sch_Spe_Id,Student_Setup_School_Specific_Category");

                string strschspecat = "Insert into Student_Setup_School_Specific_Category (Sch_Spe_Id,Cat_Id,Sch_Id) values (" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",N'1,2,3,4,5,6,7,8','" + INBL.Schoolid + "')";

                //DataConnection.ConnectionPath.ExecuteCommandafterinstall(strschspecat, cnnstring, serverinst);
                DataConnection.ConnectionPath.ExecuteCommand(strschspecat);
            }


            if (serverinst == "MySQL")
            {
                DataSet ds = new DataSet();
                //ds = DataConnection.ConnectionPath.Getdataafterinstall("CALL getmax('Sch_Spe_Id','Student_Setup_School_Specific_Category')", cnnstring, serverinst);
                ds = DataConnection.ConnectionPath.Getdata("CALL getmax('Sch_Spe_Id','Student_Setup_School_Specific_Category')");

                string strschspecat = "Insert into Student_Setup_School_Specific_Category (Sch_Spe_Id,Cat_Id,Sch_Id) values (" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",N'1,2,3,4,5,6,7,8','" + INBL.Schoolid + "')";
                //DataConnection.ConnectionPath.ExecuteCommandafterinstall(strschspecat, cnnstring, serverinst);

                DataConnection.ConnectionPath.ExecuteCommand(strschspecat);

            }


        }

        public void InsertintoSchoolspeccatadd(InstallationBL INBL)
        {
            string server = System.Configuration.ConfigurationManager.AppSettings["appserver"];
           // string server = "MSSQLServer";
            if (server == "MSSQLServer")
            {
                DataSet ds = new DataSet();
                ds = DataConnection.ConnectionPath.Getdata("exec getMax Sch_Spe_Id,Student_Setup_School_Specific_Category");

                string strschspecat = "Insert into Student_Setup_School_Specific_Category (Sch_Spe_Id,Cat_Id,Sch_Id) values (" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",N'1,2,3,4,5,6,7,8','" + INBL.Schoolid + "')";

                DataConnection.ConnectionPath.ExecuteCommand(strschspecat);
            }


            if (server == "MySQL")
            {
                DataSet ds = new DataSet();
                ds = DataConnection.ConnectionPath.Getdata("CALL getmax('Sch_Spe_Id','Student_Setup_School_Specific_Category')");

                string strschspecat = "Insert into Student_Setup_School_Specific_Category (Sch_Spe_Id,Cat_Id,Sch_Id) values (" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",N'1,2,3,4,5,6,7,8','" + INBL.Schoolid + "')";

                DataConnection.ConnectionPath.ExecuteCommand(strschspecat);

            }


        }


    }
}